import pandas as pd # type: ignore
from sqlalchemy import create_engine # type: ignore
import time
start =time.perf_counter()
# PostgreSQL 配置
postgres_host = "172.16.137.39"  
postgres_port = "5432"  
postgres_user = "a2513220218"  
postgres_password = "xawl-6043"  
postgres_database = "a2513220218" 

# CSV 文件路径
csv_file_path_CUST_AZ12 = "D:\数据库\数据仓库任务\datasets-1\datasets\source_erp\CUST_AZ12.csv"
csv_file_path_LOC_A101 = "D:\数据库\数据仓库任务\datasets-1\datasets\source_erp\LOC_A101.csv"
csv_file_path_PX_CAT_G1V2 = "D:\数据库\数据仓库任务\datasets-1\datasets\source_erp\PX_CAT_G1V2.csv"
# 建立连接字符串
conn_string = f"postgresql://{postgres_user}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"

# 使用 SQLAlchemy 创建数据库连接
engine = create_engine(conn_string)

try:
    # Step 1: 读取 CSV 文件
    df_CUST_AZ12_info = pd.read_csv(csv_file_path_CUST_AZ12)
    df_LOC_A101_info = pd.read_csv(csv_file_path_LOC_A101)
    df_PX_CAT_G1V2 = pd.read_csv(csv_file_path_PX_CAT_G1V2)
    # Step 2: 将 DataFrame 导入到 PostgreSQL 数据库
    df_CUST_AZ12_info.to_sql('erp_CUST_AZ12', engine, schema='datawarehouse', if_exists='append', index=False)
    print("数据成功导入到 datawarehouse.erp_CUST_AZ12 表中")
    # 导入到 erp_LOC_A101 表中
    df_LOC_A101_info.to_sql('erp_LOC_A101', engine, schema='datawarehouse', if_exists='append', index=False)
    print("数据成功导入到 datawarehouse.erp_LOC_A101 表中")

    df_PX_CAT_G1V2.to_sql('erp_PX_CAT_G1V2', engine, schema='datawarehouse', if_exists='append', index=False)
    print("数据成功导入到 datawarehouse.erp_PX_CAT_G1V2 表中")
except Exception as e:
    print(f"导入数据时出错: {e}")
finally:
    # 关闭数据库连接
    engine.dispose()

end = time.perf_counter()
print('Running time: %s Seconds'%(end-start))